/**
 * 
 */
package game.gamestorage.texas.db.estimation;

/**
 * This class holds the sql-queries for reading the db</br>
 * 
 * @author Witthold/Korol
 */
public final class GetterQuery {

	/**
	 * @category getCommonsOfCompetition<br>
	 * @param timestamp as unix timestamp
	 * @return int[] of competition_ids newer than timestamp && same gamedef like newest
	 */
	protected static String getCompetitionIds(long timestamp) {

		// @formatter:off
		String sql = "SELECT DISTINCT competition.id " +
				"FROM " +
				"competition JOIN (hand JOIN round) " +
				"ON " +
				"(competition.id = hand.competition_id " +
				"AND " +	
				"hand.id = round.hand_id) " +
				"WHERE " +
				"round.timestamp > " + timestamp + " " +
				"AND " +
				"competition.gamedef_id = " +
					"(SELECT competition.gamedef_id FROM competition WHERE id = " +	
						"(SELECT MAX(id) FROM competition)) " + 
				";";
		// @formatter:on
		return sql;
	}


	/**
	 * @category getCommonsOfCompetition<br>
	 * @param gamedef_id - already generated by db
	 * @return int[] of competition_ids newer than timestamp && same gamedef like newest
	 */
	protected static String getCompetitionIds(int gamedef_id) {

		// @formatter:off
		String sql = "SELECT DISTINCT competition.id " +
				"FROM " +
				"competition " +
				"WHERE " +
				"gamedef_id = " + gamedef_id + " " +		
				";";
		// @formatter:on
		return sql;
	}


	/**
	 * @category getCommonsOfCompetition<br>
	 * @param gamedef_id - already generated by db
	 * @param timestamp as unix timestamp
	 * @return int[] of competition_ids newer than timestamp && same gamedef like newest
	 */
	protected static String getCompetitionIds(int gamedef_id, long timestamp) {

		// @formatter:off
		String sql = "SELECT DISTINCT competition.id " +
		"FROM " +
		"competition JOIN (hand JOIN round) " +
		"ON " +
		"(competition.id = hand.competition_id " +
		"AND " +	
		"hand.id = round.hand_id) " +
		"WHERE " +
		"round.timestamp > " + timestamp + " " +
		"AND " +
		"competition.gamedef_id = " + gamedef_id + " " +	
//		"GROUP BY " +
//		"competition.id" +		// prefer DISTINCT for better human readability
		";";
		// @formatter:on
		return sql;
	}


	/**
	 * @category getCommonsOfCompetition<br>
	 * @return MAX(id)
	 */
	protected static String getLatestCompetitionId() {

		// @formatter:off
		String sql = "SELECT MAX(id) " +
				"FROM " +
				"competition " +
				";";
		// @formatter:on
		return sql;
	}


	/**
	 * @category getCommonsOfCompetition<br>
	 * @return MAX(id)
	 */
	protected static String getLatestHandId() {

		// @formatter:off
		String sql = "SELECT MAX(id) " +
				"FROM " +
				"hand " +
				";";
		// @formatter:on
		return sql;
	}


	/**
	 * @category getCommonsOfCompetition<br>
	 * @param competition_id already generated by db
	 * @return player_id, cash_init, rank
	 */
	protected static String getCompetitionPlayerMaps(int competition_id) {

		// @formatter:off
		String sql = "SELECT player_id, cash_init, rank " +
				"FROM " +
				"competition_player_map " +
				"WHERE " +
				"competition_player_map.competition_id = " + competition_id +
				";";
		// @formatter:on
		return sql;
	}


	/**
	 * @category getCommonsOfCompetition<br>
	 * @param competition_id already generated by db
	 * @return player.id, player.alias
	 */
	protected static String getPlayersInCompetition(int competition_id) {

		// @formatter:off
		String sql = "SELECT player.id, player.alias " +
				"FROM " +
				"player JOIN competition_player_map " +
				"ON " +
				"player.id = competition_player_map.player_id " +
				"WHERE " +
				"competition_player_map.competition_id = " + competition_id +
				";";
		// @formatter:on
		return sql;
	}
	
	
	/**
	 * @category getCommonsOfCompetition<br>
	 * @param competition_id already generated by db
	 * @return player.alias, player.id
	 */
	protected static String getPlayersInCompetitionSI(int competition_id) {

		// @formatter:off
		String sql = "SELECT player.alias, player.id " +
				"FROM " +
				"player JOIN competition_player_map " +
				"ON " +
				"player.id = competition_player_map.player_id " +
				"WHERE " +
				"competition_player_map.competition_id = " + competition_id +
				";";
		// @formatter:on
		return sql;
	}


	/**
	 * @category getStatsForSHC<br>
	 * @param player_id already generated by db
	 * @return hand_player_map for EnemyCardsOpen with:<br>
	 *         hand_player_map.id, hand_player_map.position, hand_player_map.cash_init, hand_player_map.cash_delta, hand_player_map.hand_id
	 */
	protected static String getHandPlayerMapWhereEnemyCardsOpenAllTime(int player_id) {

		// @formatter:off
		String sql = "SELECT DISTINCT " +
				"hand_player_map.id, hand_player_map.position, hand_player_map.cash_init, hand_player_map.cash_delta, hand_player_map.hand_id " +
				"FROM " +
				"hand_player_map JOIN card_of_player " +
				"ON " +
				"card_of_player.hand_player_map_id = hand_player_map.id " +
				"WHERE " +
				"hand_player_map.player_id = " + player_id + " " +
				";";
		// @formatter:on
		return sql;
	}


	/**
	 * @category getStatsForSHC<br>
	 * @param player_id already generated by db
	 * @param competition_id already generated by db
	 * @param lastEvaledHandId already generated by db
	 * @return hand_player_map for EnemyCardsOpen with:<br>
	 *         hand_player_map.id, hand_player_map.position, hand_player_map.cash_init, hand_player_map.cash_delta, hand_player_map.hand_id
	 */
	protected static String getHandPlayerMapWhereEnemyCardsOpenPerComp(int player_id, int competition_id, int lastEvaledHandId) {

		// @formatter:off
		String sql = "SELECT DISTINCT " +
				"hand_player_map.id, hand_player_map.position, hand_player_map.cash_init, hand_player_map.cash_delta, hand_player_map.hand_id " +
				"FROM " +
				"hand_player_map JOIN (card_of_player JOIN hand) " +
				"ON " +
				"(card_of_player.hand_player_map_id = hand_player_map.id " +
				"AND " +	
				"hand.id = hand_player_map.hand_id) " +
				"WHERE " +
				"hand_player_map.player_id = " + player_id + " " +
				"AND " +
				"hand.competition_id = " + competition_id + " " +
				"AND " +
				"hand.id > " + lastEvaledHandId + " " +
				";";
		// @formatter:on
		return sql;
	}


	/**
	 * @category getStatsForSHC<br>
	 * @param hand_id already generated by db
	 * @return NumActivePlayers at hand
	 */
	protected static String getNumActivePlayers(int hand_id) {

		// @formatter:off
		String sql = "SELECT COUNT(id) " +
				"FROM " +
				"hand_player_map " +
				"WHERE " +
				"hand_id = " + hand_id +
				";";
		// @formatter:on
		return sql;
	}
	
	/**
	 * @category getStatsForSHC<br>
	 * @param hand_id already generated by db
	 * @return NumActivePlayers at hand
	 */
	protected static String getCurrentBet(long bid, long pid, long hid) {

		// @formatter:off
		String sql = "SELECT SUM(betsize) "+
			"FROM bet "+
			"INNER JOIN round ON bet.round_id = round.id "+
			"INNER JOIN hand ON round.hand_id = hand.id "+
			"WHERE player_id = "+pid+" "+
			"AND hand_id = "+hid+" "+
			"AND bet.id < "+bid+" "+
			";";
		// @formatter:on
		return sql;
	}
	
	/**
	 * @category getStatsForSHC<br>
	 * @param hand_id already generated by db
	 * @return NumActivePlayers at hand
	 */
	protected static String getHighestBet(long bid, long hid) {

		// @formatter:off
		String sql = "SELECT MAX(betsize) "+
			"FROM bet "+
			"INNER JOIN round ON bet.round_id = round.id "+
			"INNER JOIN hand ON round.hand_id = hand.id "+
			"WHERE hand_id = "+hid+" "+
			"AND bet.id < "+bid+" "+
			";";
		// @formatter:on
		return sql;
	}


	/**
	 * @category getStatsForSHC<br>
	 * @param hand_player_map_id already generated by db
	 * @return int[] EnemyCards
	 */
	protected static String getHoleCards(int hand_player_map_id) {

		// @formatter:off
		String sql = "SELECT " +
				"value " +
				"FROM " +
				"card_of_player " +
				"WHERE " +
				"hand_player_map_id = " + hand_player_map_id + " " +
				";";
		// @formatter:on
		return sql;
	}
	
	/**
	 * @category getStatsForSHC<br>
	 * @param hand_player_map_id already generated by db
	 * @return int[] EnemyCards
	 */
	protected static String getAllHoleCardsOfHand(int hand_id) {

		// @formatter:off
		String sql = "SELECT player_id, value "+
			"FROM card_of_player "+
			"INNER JOIN hand_player_map "+ 
			"ON card_of_player.hand_player_map_id = hand_player_map.id "+
			"WHERE hand_id = "+hand_id+" "+
			"AND player_id IN (SELECT player_id "+
			"FROM bet WHERE "+
			"round_id = (SELECT MAX(id) FROM round "+
			"WHERE hand_id = "+hand_id+") "+
			"AND action >=0);";
		// @formatter:on
		return sql;
	}
	
	/**
	 * @category getStatsForFlop<br>
	 * @param round_id already generated by db
	 * @return int[] EnemyCards
	 */
	protected static String getBoardCards(int round_id) {

		// @formatter:off
		String sql = "SELECT " +
				"value " +
				"FROM " +
				"card_common " +
				"WHERE " +
				"round_id = " + round_id + " " +
				";";
		// @formatter:on
		return sql;
	}


	/**
	 * @category getStatsForSHC<br>
	 * @param player_id already generated by db
	 * @param hand_id already generated by db
	 * @return long[] BetOfFirstAction with:<br>
	 *         action, betsize, potsize, timestamp, round_id
	 */
	protected static String getFirstBetOfRound(int player_id, int hand_id) {

		// @formatter:off
		String sql = "SELECT " +
				"MIN(id), action, betsize, potsize, timestamp, round_id " +
				"FROM " +
				"bet " +
				"WHERE " +
				"player_id = " + player_id + " " +
				"AND " +					
				"round_id = " +
					"(SELECT MIN(id) FROM round WHERE hand_id = " +	hand_id + ")" + 
				";";
		// @formatter:on
		return sql;
	}

	/**
	 * @category getStatsForSHC<br>
	 * @param round_id already generated by db
	 * @param playersFirstBetId already generated by db
	 * @return long[] PreviousActions with:<br>
	 *         action
	 */
	protected static String getPreviousActions(long round_id, long playersFirstBetId) { 

		// @formatter:off
		String sql = "SELECT " +
				"action " +
				"FROM " +
				"bet " +
				"WHERE " +
				"round_id = " + round_id + " " +
				"AND " +					
				"id < " + playersFirstBetId + 
				";";
		// @formatter:on
		return sql;
	}
	
	
	/**
	 * @category getStatsForSHC<br>
	 * @param round_id already generated by db
	 * @param playersFirstBetId already generated by db
	 * @return long[] PreviousActions with:<br>
	 *         action
	 */
	protected static String getPreviousActionsMulti(long round_id, long playersFirstBetId) { 

		// @formatter:off
		String sql = "SELECT " +
				"action " +
				"FROM " +
				"bet " +
				"WHERE " +
				"round_id = " + round_id + " " +
				"AND " +					
				"id < " + playersFirstBetId + 
				";";
		// @formatter:on
		return sql;
	}


	/**
	 * @category getStatsForSHC<br>
	 * @param player_id already generated by db
	 * @param competition_id already generated by db
	 * @param lastEvaledHandId already generated by db
	 * @return ArrayList of int[] Hands with:<br>
	 *         hand.id, hand.sb, hand.bb, hand.ante, hand.finalpot
	 */
	protected static String getHandsCO(int player_id, int competition_id, int lastEvaledHandId) { // TODO Improve by UNION, INTERSECT?

		// @formatter:off
		String sql = "SELECT DISTINCT " +
				"hand.id, hand.sb, hand.bb, hand.ante, hand.finalpot " +
				"FROM " +
				"hand JOIN (hand_player_map JOIN card_of_player) " +
				"ON " +
				"(card_of_player.hand_player_map_id = hand_player_map.id " +
				"AND " +	
				"hand.id = hand_player_map.hand_id) " +
				"WHERE " +
				"hand_player_map.player_id = " + player_id + " " +	
				"AND " +
				"hand.competition_id = " + competition_id + " " +
				"AND " +
				"hand.id > " + lastEvaledHandId + " " +
				";";
		// @formatter:on
		return sql;
	}


	/**
	 * @category getStatsForSHC<br>
	 * @param player_id already generated by db
	 * @param competition_id already generated by db
	 * @param lastEvaledHandId already generated by db
	 * @return ArrayList of int[] Hands with:<br>
	 *         hand_player_map for EnemyCardsOpen with:<br>
	 *         hand_player_map.id, hand_player_map.position, hand_player_map.cash_init, hand_player_map.cash_delta, hand_player_map.hand_id
	 */
	protected static String getHandPlayerMapCardsClosed(int player_id, int competition_id, int lastEvaledHandId) {

		// @formatter:off
		String sql = "SELECT " +
				"hand_player_map.id, hand_player_map.position, hand_player_map.cash_init, hand_player_map.cash_delta, hand_player_map.hand_id " +
				"FROM " +
				"hand_player_map JOIN hand " +
				"ON " +
				"(hand_player_map.hand_id = hand.id) " +				
				"WHERE " +
				"hand_player_map.player_id = " + player_id + " " +
				"AND " +
				"hand.id > " + lastEvaledHandId + " " +
				"AND " +
				"hand.competition_id = " + competition_id + " " +
				"AND " +
				"NOT EXISTS " +
				"(SELECT hand_player_map_id " +
					"FROM " +
					"card_of_player " +
					"WHERE " +
					"card_of_player.hand_player_map_id = hand_player_map.id) " +
				";";
		// @formatter:on
		return sql;
	}
	
	/**
	 * @category getStatsForSHC<br>
	 * @param player_id already generated by db
	 * @param competition_id already generated by db
	 * @param lastEvaledHandId already generated by db
	 * @return ArrayList of int[] Hands with:<br>
	 *         hand.id, hand.sb, hand.bb, hand.ante, hand.finalpot
	 */
	protected static String getHandsCC(int player_id, int competition_id, int lastEvaledHandId) { // TODO Improve by UNION, INTERSECT?

		// @formatter:off
		String sql = "SELECT DISTINCT " +
				"hand.id, hand.sb, hand.bb, hand.ante, hand.finalpot " +
				"FROM " +
				"hand JOIN hand_player_map " +
				"ON " +				
				"(hand.id = hand_player_map.hand_id) " +
				"WHERE " +
				"hand_player_map.player_id = " + player_id + " " +	
				"AND " +
				"hand.competition_id = " + competition_id + " " +
				"AND " +
				"hand.id > " + lastEvaledHandId + " " +
				"AND " +
				"NOT EXISTS " +
				"(SELECT hand_player_map_id " +
					"FROM " +
					"card_of_player " +
					"WHERE " +
					"card_of_player.hand_player_map_id = hand_player_map.id) " +
				";";
		// @formatter:on
		return sql;
	}	
	
	/**
	 * @category getStatsForSHC<br>
	 * @param player_id already generated by db
	 * @param competition_id already generated by db
	 * @param lastEvaledHandId already generated by db
	 * @return ArrayList of long[] firstBetOfRound with:<br>
	 *         bet.round_id, MIN(bet.id), bet.action, bet.betsize, bet.potsize, bet.timestamp, bet.round_id
	 */
	protected static String getFirstBetsOfRound(int player_id, int competition_id, int lastEvaledHandId) {

		// @formatter:off
		String sql = "SELECT " +
				"bet.round_id, MIN(bet.id), bet.action, bet.betsize, bet.potsize, bet.timestamp, bet.round_id " +
				"FROM " +
				"bet JOIN (round JOIN hand) " +
				"ON "   +
				"(bet.round_id = round.id " +
				"AND " +	
				"round.hand_id = hand.id) " +
				"WHERE " +
				"bet.player_id = " + player_id + " " +	
				"AND " +
				"hand.competition_id = " + competition_id + " " +
				"AND " +
				"hand.id > " + lastEvaledHandId + " " +
				"AND " +					
				"round.id = (SELECT MIN(id) FROM round WHERE hand_id = hand.id) " + 
				"GROUP BY round.id" +	
				";";
		// @formatter:on
		return sql;
	}
	
	/**
	 * @category getStatsForSHC<br>
	 * @param player_id already generated by db
	 * @param competition_id already generated by db
	 * @param lastEvaledHandId already generated by db
	 * @return ArrayList of long[] firstBetOfRound with:<br>
	 *         bet.round_id, MIN(bet.id), bet.action, bet.betsize, bet.potsize, bet.timestamp, bet.round_id
	 */
	protected static String getFlopBetsOfRound(int player_id, int competition_id, int lastEvaledHandId) {

		// @formatter:off
		String sql = "SELECT t1.* " +
				"FROM " + 
				"(SELECT bet.round_id AS round_id, MIN(bet.id), bet.action, bet.betsize, bet.potsize, bet.timestamp, bet.round_id AS r2 "+
				"FROM bet JOIN (round JOIN hand) ON (bet.round_id = round.id AND round.hand_id = hand.id) "+
				"WHERE bet.player_id = " + player_id + " " +
				"AND hand.competition_id = " + competition_id + " " +
				"AND hand.id > " + lastEvaledHandId + " " +
				"GROUP BY round.id) AS t1 " + 
				"LEFT JOIN card_common ON t1.round_id = card_common.round_id "+
				"GROUP BY t1.round_id HAVING count(*)=3;";
		// @formatter:on
		return sql;
	}
	
	/**
	 * @category getStatsForSHC<br>
	 * @param player_id already generated by db
	 * @param competition_id already generated by db
	 * @param lastEvaledHandId already generated by db
	 * @return ArrayList of long[] rounds with:<br>
	 *         round.hand_id, round.id, round.timestamp
	 */
	protected static String getFirstRounds(int player_id, int competition_id, int lastEvaledHandId) {

		// @formatter:off
		String sql = "SELECT " +
				"MIN(round.hand_id), round.id, round.timestamp " +
				"FROM " +
				"bet JOIN (round JOIN hand) " +
				"ON " +
				"(bet.round_id = round.id " +
				"AND " +	
				"round.hand_id = hand.id) " +
				"WHERE " +
				"bet.player_id = " + player_id + " " +	
				"AND " +
				"hand.competition_id = " + competition_id + " " +
				"AND " +
				"hand.id > " + lastEvaledHandId + " " +
				"GROUP BY hand.id" +	
				";";
		// @formatter:on
		return sql;
	}
	
	/**
	 * @category getStatsForFlop<br>
	 * @param player_id already generated by db
	 * @param competition_id already generated by db
	 * @param lastEvaledHandId already generated by db
	 * @return ArrayList of long[] rounds with:<br>
	 *         round.hand_id, round.id, round.timestamp
	 */
	protected static String getFlopRounds(int player_id, int competition_id, int lastEvaledHandId) {

		// @formatter:off
		String sql = "SELECT " +
				"round.hand_id, round.id, round.timestamp " +
				"FROM " +
				"bet JOIN (round JOIN hand) " +
				"ON " +
				"(bet.round_id = round.id " +
				"AND " +	
				"round.hand_id = hand.id) " +
				"WHERE " +
				"bet.player_id = " + player_id + " " +	
				"AND " +
				"hand.competition_id = " + competition_id + " " +
				"AND " +
				"hand.id > " + lastEvaledHandId + " " +
				"AND " +
				"round.id IN (SELECT round_id FROM (SELECT COUNT(*) AS num, round_id FROM card_common GROUP BY round_id HAVING num=3) AS t1)" +
				";";
		// @formatter:on
		return sql;
	}

	//TODO
	protected static String getRiverRounds(int playerId, int compId, int lastEvaledHandId) {
		// @formatter:off
		String sql = "SELECT " +
				"round.hand_id, round.id, round.timestamp " +
				"FROM " +
				"bet JOIN (round JOIN hand) " +
				"ON " +
				"(bet.round_id = round.id " +
				"AND " +	
				"round.hand_id = hand.id) " +
				"WHERE " +
				"bet.player_id = " + playerId + " " +	
				"AND " +
				"hand.competition_id = " + compId + " " +
				"AND " +
				"hand.id > " + lastEvaledHandId + " " +
				"AND " +
				"round.id IN (SELECT MAX(round_id) FROM "+
				"(SELECT hand_id, round_id FROM PokerBot.card_common INNER JOIN round ON card_common.round_id = round.id GROUP BY round_id HAVING count(*)=1) AS t1 "+
				"GROUP BY hand_id HAVING MIN(round_id)!=MAX(round_id))" +
				";";
		// @formatter:on
		return sql;
	}

	//TODO
	protected static String getRiverBetsOfRound(int playerId, int compId, int lastEvaledHandId) {

		// @formatter:off
		String sql = "SELECT bet.round_id AS round_id, MIN(bet.id), bet.action, bet.betsize, bet.potsize, bet.timestamp, bet.round_id AS r2 "+
				"FROM bet JOIN (round JOIN hand) ON (bet.round_id = round.id AND round.hand_id = hand.id) "+
				"WHERE bet.player_id = " + playerId + " " +
				"AND hand.competition_id = " + compId + " " +
				"AND hand.id > " + lastEvaledHandId + " " +
				"GROUP BY round.id " +
				"HAVING round.id IN (SELECT MAX(round_id) "+
				"FROM (SELECT hand_id, round_id FROM PokerBot.card_common INNER JOIN round ON card_common.round_id = round.id "+
				"GROUP BY round_id HAVING count(*)=1) AS t1 GROUP BY hand_id HAVING MIN(round_id)!=MAX(round_id));";
		// @formatter:on
		return sql;
	}


	protected static String getTurnBetsOfRound(int playerId, int compId, int lastEvaledHandId) {
		// @formatter:off
		String sql = "SELECT bet.round_id AS round_id, MIN(bet.id), bet.action, bet.betsize, bet.potsize, bet.timestamp, bet.round_id AS r2 "+
		"FROM bet JOIN (round JOIN hand) ON (bet.round_id = round.id AND round.hand_id = hand.id) "+
		"WHERE bet.player_id = " + playerId + " " +
		"AND hand.competition_id = " + compId + " " +
		"AND hand.id > " + lastEvaledHandId + " " +
		"GROUP BY round.id " +
		"HAVING round.id IN "+
		"(SELECT MIN(round_id) FROM (SELECT hand_id, round_id FROM PokerBot.card_common INNER JOIN round ON card_common.round_id = round.id "+
		"GROUP BY round_id HAVING count(*)=1) AS t1 GROUP BY hand_id);";
		// @formatter:on
		return sql;
	}


	protected static String getTurnRounds(int playerId, int compId, int lastEvaledHandId) {
		// @formatter:off
		String sql = "SELECT " +
				"round.hand_id, round.id, round.timestamp " +
				"FROM " +
				"bet JOIN (round JOIN hand) " +
				"ON " +
				"(bet.round_id = round.id " +
				"AND " +	
				"round.hand_id = hand.id) " +
				"WHERE " +
				"bet.player_id = " + playerId + " " +	
				"AND " +
				"hand.competition_id = " + compId + " " +
				"AND " +
				"hand.id > " + lastEvaledHandId + " " +
				"AND " +
				"round.id IN (SELECT MIN(round_id) FROM "+
				"(SELECT hand_id, round_id FROM PokerBot.card_common INNER JOIN round ON card_common.round_id = round.id GROUP BY round_id HAVING count(*)=1) AS t1 "+
				"GROUP BY hand_id)" +
				";";
		// @formatter:on
		return sql;
	}

	
}
